'''
Created on May 9, 2012

@author: peng
'''
import MySQLdb
from DBUtils.PooledDB import PooledDB

class MySQLdbWrapper:

    _default = {'host':'localhost',
                    'user':'root',
                    'passwd':'P@55word',
                    'db':'test', }
    
    _failover = {'host':'10.2.1.162',
                    'user':'peng',
                    'passwd':'P@55word',
                    'db':'test', }

    def __init__(self, conf=_default):
        self._pool = PooledDB(creator=MySQLdb, mincached=0, maxcached=10, maxshared=10, maxusage=10000,
                              failures=(MySQLdb.OperationalError),
                              host=conf['host'], user=conf['user'],
                              passwd=conf['passwd'], db=conf['db'],
                              charset="utf8", use_unicode=False, port=3306,
                              setsession=['SET AUTOCOMMIT = 1'])
    
    def failover(self):
        self.__init__(self._failover)
    
    def get_conn(self):  
        return self._pool.connection()

_db = MySQLdbWrapper()


def execute_sql(sql):
    conn = None
    cursor = None
    try:
        conn = _db.get_conn()
        cursor = conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        return result
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    try:
        sql = 'select * from screenshots'
        result = execute_sql(sql)
        print result
    except MySQLdb.OperationalError, ex:
        global _db
        _db.failover()
        result = execute_sql(sql)
        print result
    except Exception, ex:
        print ex
